﻿using System; 
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Matrix.Common.DBUtility;
using Matrix.MaKaYunShang.Model;

namespace Matrix.MaKaYunShang.DAL
{
	public partial class ConsumerDAL : BaseDAL<ConsumerDAL>
	{
        #region 默认方法
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Consumer model)
        {
            var strSql = new StringBuilder();
            strSql.Append("insert into Consumer(");
            strSql.Append("MxWechatInfoUserId,MxProjectInfoId,Avatar,NickName,RealName,Mobile,Email,Sex,Birth,RegTime,IpAddress,Longitude,Latitude,ShopId,ShopAccountId,IsDelete");
            strSql.Append(") values (");
            strSql.Append("@MxWechatInfoUserId,@MxProjectInfoId,@Avatar,@NickName,@RealName,@Mobile,@Email,@Sex,@Birth,@RegTime,@IpAddress,@Longitude,@Latitude,@ShopId,@ShopAccountId,@IsDelete");
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {            
                        new SqlParameter("@MxWechatInfoUserId", SqlDbType.Int,4) ,  
                        new SqlParameter("@MxProjectInfoId", SqlDbType.Int,4) , 
                        new SqlParameter("@Avatar", SqlDbType.NVarChar,500) ,            
                        new SqlParameter("@NickName", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@RealName", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Mobile", SqlDbType.NVarChar,20) ,            
                        new SqlParameter("@Email", SqlDbType.NVarChar,100) ,            
                        new SqlParameter("@Sex", SqlDbType.Int,4) ,            
                        new SqlParameter("@Birth", SqlDbType.DateTime) ,            
                        new SqlParameter("@RegTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@IpAddress", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Longitude", SqlDbType.NVarChar,16) ,            
                        new SqlParameter("@Latitude", SqlDbType.NVarChar,16) ,            
                        new SqlParameter("@ShopId", SqlDbType.Int,4) ,            
                        new SqlParameter("@ShopAccountId", SqlDbType.Int,4) ,            
                        new SqlParameter("@IsDelete", SqlDbType.Bit,1)             
              
            };

            parameters[0].Value = model.MxWechatInfoUserId;
            parameters[1].Value = model.MxProjectInfoId;
            parameters[2].Value = model.Avatar;
            parameters[3].Value = model.NickName;
            parameters[4].Value = model.RealName;
            parameters[5].Value = model.Mobile;
            parameters[6].Value = model.Email;
            parameters[7].Value = model.Sex;
            parameters[8].Value = model.Birth;
            parameters[9].Value = model.RegTime;
            parameters[10].Value = model.IpAddress;
            parameters[11].Value = model.Longitude;
            parameters[12].Value = model.Latitude;
            parameters[13].Value = model.ShopId;
            parameters[14].Value = model.ShopAccountId;
            parameters[15].Value = model.IsDelete; 
            var obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            return obj == null ? 0 : Convert.ToInt32(obj);
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Consumer model)
        {
            var strSql = new StringBuilder();
            strSql.Append("update Consumer set ");

            strSql.Append(" MxWechatInfoUserId = @MxWechatInfoUserId , ");
            strSql.Append(" MxProjectInfoId = @MxProjectInfoId , ");
            strSql.Append(" Avatar = @Avatar , ");
            strSql.Append(" NickName = @NickName , ");
            strSql.Append(" RealName = @RealName , ");
            strSql.Append(" Mobile = @Mobile , ");
            strSql.Append(" Email = @Email , ");
            strSql.Append(" Sex = @Sex , ");
            strSql.Append(" Birth = @Birth , ");
            strSql.Append(" RegTime = @RegTime , ");
            strSql.Append(" IpAddress = @IpAddress , ");
            strSql.Append(" Longitude = @Longitude , ");
            strSql.Append(" Latitude = @Latitude , ");
            strSql.Append(" ShopId = @ShopId , ");
            strSql.Append(" ShopAccountId = @ShopAccountId , ");
            strSql.Append(" IsDelete = @IsDelete  ");
            strSql.Append(" where Id=@Id ");

            SqlParameter[] parameters = {
			            new SqlParameter("@Id", SqlDbType.Int,4) ,            
                        new SqlParameter("@MxWechatInfoUserId", SqlDbType.Int,4) , 
                        new SqlParameter("@MxProjectInfoId", SqlDbType.Int,4) ,    
                        new SqlParameter("@Avatar", SqlDbType.NVarChar,500) ,            
                        new SqlParameter("@NickName", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@RealName", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Mobile", SqlDbType.NVarChar,20) ,            
                        new SqlParameter("@Email", SqlDbType.NVarChar,100) ,            
                        new SqlParameter("@Sex", SqlDbType.Int,4) ,            
                        new SqlParameter("@Birth", SqlDbType.DateTime) ,            
                        new SqlParameter("@RegTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@IpAddress", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Longitude", SqlDbType.NVarChar,16) ,            
                        new SqlParameter("@Latitude", SqlDbType.NVarChar,16) ,            
                        new SqlParameter("@ShopId", SqlDbType.Int,4) ,            
                        new SqlParameter("@ShopAccountId", SqlDbType.Int,4) ,            
                        new SqlParameter("@IsDelete", SqlDbType.Bit,1)             
              
            };

            parameters[0].Value = model.Id;
            parameters[1].Value = model.MxWechatInfoUserId;
            parameters[2].Value = model.MxProjectInfoId;
            parameters[3].Value = model.Avatar;
            parameters[4].Value = model.NickName;
            parameters[5].Value = model.RealName;
            parameters[6].Value = model.Mobile;
            parameters[7].Value = model.Email;
            parameters[8].Value = model.Sex;
            parameters[9].Value = model.Birth;
            parameters[10].Value = model.RegTime;
            parameters[11].Value = model.IpAddress;
            parameters[12].Value = model.Longitude;
            parameters[13].Value = model.Latitude;
            parameters[14].Value = model.ShopId;
            parameters[15].Value = model.ShopAccountId;
            parameters[16].Value = model.IsDelete; 
            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int Id)
        {
            var strSql = new StringBuilder();
            strSql.Append("update Consumer set IsDelete=1 ");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 批量删除一批数据
        /// </summary>
        public bool DeleteList(string Idlist)
        {
            var strSql = new StringBuilder();
            strSql.Append("update Consumer set IsDelete=1 ");
            strSql.Append(" where Id in (" + Idlist + ")  ");
            return DbHelperSQL.ExecuteSql(strSql.ToString()) > 0;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DataSet GetModel(int Id)
        {
            var strSql = new StringBuilder();
            strSql.Append("select Id, MxWechatInfoUserId, MxProjectInfoId, Avatar, NickName, RealName, Mobile, Email, Sex, Birth, RegTime, IpAddress, Longitude, Latitude, ShopId, ShopAccountId, IsDelete  ");
            strSql.Append("  from Consumer ");
            strSql.Append(" where Id=@Id  and IsDelete=0");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;

            var ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            return ds.Tables[0].Rows.Count > 0 ? ds : null;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            var strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM Consumer where IsDelete=0 ");
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" and " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        #endregion
		
		#region 扩展方法
        /// <summary>
        /// 通过OPENID和公众号记录ID寻找消费者记录
        /// </summary>
        /// <param name="mxWechatInfoUserId"></param>
        /// <returns></returns>
        public Consumer GetConsumerInfoByMxWechatInfoUserId(int mxWechatInfoUserId)
        {
            var strSql = new StringBuilder();
            strSql.Append("SELECT c.Id, c.MxWechatInfoUserId, c.MxProjectInfoId, c.Avatar, c.NickName, c.RealName, c.Mobile, c.Email, c.Sex, c.Birth, c.RegTime, c.IpAddress, c.Longitude, c.Latitude, c.ShopId, c.ShopAccountId, c.IsDelete  ");
            strSql.Append("FROM Consumer c ");
            strSql.Append("WHERE c.MxWechatInfoUserId=@MxWechatInfoUserId");

            SqlParameter[] parameters = {
					new SqlParameter("@MxWechatInfoUserId", SqlDbType.Int,4)
			};
            parameters[0].Value = mxWechatInfoUserId;

            return DbHelperSQL.ExecuteReader<Consumer>(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 得到一个消费者对象实体
        /// </summary>
        public Consumer GetConsumerInfo(int Id)
        {
            var strSql = new StringBuilder();
            strSql.Append("select Id, MxWechatInfoUserId, MxProjectInfoId, Avatar, NickName, RealName, Mobile, Email, Sex, Birth, RegTime, IpAddress, Longitude, Latitude, ShopId, ShopAccountId, IsDelete  ");
            strSql.Append("  from Consumer ");
            strSql.Append(" where Id=@Id  and IsDelete=0");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;

            return DbHelperSQL.ExecuteReader<Consumer>(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 获取分页数据
        /// </summary>
        public List<Consumer> GetPagingList(int pageIndex, int pageSize, string keyWord, string shopIds, ref int totalCount, bool isMag = false)
        {
            var strWhere = new StringBuilder(" IsDelete=0 ");
            var parameters = new List<SqlParameter>();
            int idSearch;
            if (isMag)
            {
                if (!string.IsNullOrEmpty(keyWord))
                {
                    strWhere.Append(" and ([NickName] like @keyWord or ShopId in (" + shopIds + ")");
                    if (int.TryParse(keyWord, out idSearch))
                    {
                        strWhere.Append(" or Id=" + keyWord);
                    }
                    strWhere.Append(")");
                    parameters.Add(new SqlParameter { ParameterName = "@keyWord", Value = "%" + keyWord + "%" });
                }
            }
            else
            {
                if (!string.IsNullOrEmpty(keyWord))
                {
                    strWhere.Append(" and ([NickName] like @keyWord");
                    if (int.TryParse(keyWord, out idSearch))
                    {
                        strWhere.Append(" or Id=" + keyWord);
                    }
                    strWhere.Append(")");
                    parameters.Add(new SqlParameter { ParameterName = "@keyWord", Value = "%" + keyWord + "%" });
                }
                if (!string.IsNullOrEmpty(shopIds))
                {
                    strWhere.Append(" and ShopId in (" + shopIds + ")");
                }
            }
            return GetSimpleListPage<Consumer>(pageIndex, pageSize, "Consumer as cs", null, "cs.*,CouponCount=(select count(*) from ConsumerCoupon where ConsumerId=cs.Id and IsDelete=0)", "Id desc", strWhere.ToString(), ref totalCount, parameters.ToArray());
        }
		#endregion
	}
}
